import datetime
import os
import re

import openpyxl
from QuickTools import tools_pmc
import tqdm


def dict_colf(wbbb: openpyxl.workbook.workbook.Workbook):
    wsss = wbbb.worksheets[0]
    dict1 = {}
    for ii in range(1, wsss.max_column + 1):
        dict1[wsss.cell(2, ii).value] = ii
    return dict1


def a3():
    root_path = r"\\192.168.70.101\19计划管理部\01.计划\1，数据处理工具\file"
    path_list = []
    for ii in os.walk(root_path):
        for jj in ii[2]:
            # i[0]是当前文件夹的绝对路径，j是文件名
            path = os.path.join(ii[0], jj)
            if "物料信息表" in path and "source_file_Ⅱ" not in path and "专用" not in path and ".xlsx" in path:
                if tools_pmc.detect_date(re.findall(r"\d{4}\.\d{2}\.\d{2}", path)[0], 1) >= datetime.datetime(2024, 7,
                                                                                                              31):
                    print(path)
                    path_list.append(path)
    return path_list


def a1(x):
    for ii in range(3, wss.max_row + 1):
        if wss.cell(ii, 1).value == x:
            return wss.cell(ii, dict_col["未请购采购量"]).value


def vlookup(lookup_value, table_array, col_index_num):
    for row in table_array:
        if str(row[0].value) == str(lookup_value):
            return row[col_index_num - 1].value


wb = openpyxl.load_workbook(tools_pmc.lj_wlxxb)
ws = wb.worksheets[0]
path_list = a3()
ws.cell(1, 1).value = "日期"
bool1 = ws.max_column + 1
ws.cell(2, bool1).value = "异常"
for i in path_list:
    print(i)
    wbb = openpyxl.load_workbook(i, read_only=True)
    wss = wbb.worksheets[0]
    table_array_wlxxb = list(wss['A2':'AE' + str(wss.max_row)])
    ws_max_col = ws.max_column + 1
    dict_col = dict_colf(wbb)
    ws.cell(2, ws_max_col).value = tools_pmc.detect_date(re.findall(r"\d{4}\.\d{2}\.\d{2}", i)[0], 2)
    for j in tqdm.tqdm(range(3, 5250)):
        ws.cell(j, ws_max_col).value = vlookup(ws.cell(j, 1).value, table_array_wlxxb, dict_col["未请购采购量"])
        if ws.cell(j, ws_max_col).value is None:
            ws.cell(j, ws_max_col).value = 0
        if ws.cell(j, ws_max_col-1).value is not None and ws.cell(j, ws_max_col).value > ws.cell(j, ws_max_col-1).value:
            ws.cell(j, ws_max_col).fill = tools_pmc.fill_red
            ws.cell(j, bool1).value = "异常"

wb.save(r"C:\Users\10352\Desktop\1.xlsx")
